package xyz.floatinglife.service;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

/**
 * 2022年1月17日10点38分
 *
 * excel批量服务类
 *
 * @author flashylife
 */
public class ExcelBatchService {

    /**
     * 第一个excel的列数
     */
    private int firstCellNum = 0;
    /**
     * 忽略的列数
     */
    private int ignoreCellNum = 0;


    public String handleExcel(String path, JTextArea jTextArea) {
        if (StringUtils.isEmpty(path)) {
            return "请输入要合并的文件路径!";
        }
        File file = new File(path);
        if (!file.isDirectory()) {
            return "请确认输入的路径是否为文件夹!";
        }
        File[] files = file.listFiles();
        boolean firstFileFlag = true;
        int i = 1;
        for (File file1 : files) {
            String name = file1.getName();
            boolean aContinue = isContinue(name);
            if (aContinue) {
                continue;
            }
            String fileName = path + File.separator + name;
            jTextArea.append("开始执行文件：" + fileName + "\n");
            System.out.println("开始执行文件：" + fileName + "\n");
            try {
                readAndWriteExcel(firstFileFlag, fileName, jTextArea);
            } catch (Exception e) {
                jTextArea.append("请检查文件是否存在:" + fileName + "\n");
                e.printStackTrace();
            }
            firstFileFlag = false;
            i++;
        }
        jTextArea.append("============================================================" + "\n");
        jTextArea.append("共读取" + i + "个文件!");
        System.out.println("共读取" + i + "个文件!");
        return "success";
    }

    private synchronized void readAndWriteExcel(boolean flag, String file, JTextArea jTextArea) throws Exception {
        String f = file.substring(0, file.lastIndexOf(File.separator) + 1) + "new.xlsx";

        Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
        Sheet sheet = workbook.getSheetAt(0);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        List<Integer> list = new ArrayList<>();
        for (int i = 0; i < physicalNumberOfRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            list.add(lastCellNum);
        }
        Collections.sort(list);
        int totalCells = list.get(list.size() - 1);

        Workbook workbook1;
        Sheet sheet1;
        if (flag) {
            workbook1 = new XSSFWorkbook();
            sheet1 = workbook1.createSheet();

            File temp = new File(f);
            if (temp.exists()) {
                temp.delete();
            }
        } else {
            workbook1 = WorkbookFactory.create(new FileInputStream(f));
            sheet1 = workbook1.getSheetAt(0);
        }

        for (int a = 0; a < physicalNumberOfRows; a++) {
            Row row = sheet.getRow(a);
            if (row == null) {
                continue;
            }
            Row row1;
            if (flag) {
                row1 = sheet1.createRow(a);
            } else {
                row1 = sheet1.getRow(a);
            }
            for (int j = 0; j < totalCells; j++) {
                Cell cell = row.getCell(j);
                String s = "";
                if (cell != null) {
                    s = getCellValue(cell);
                }
                if (flag) {
                    row1.createCell(j).setCellValue(s);
                } else {
                    if (j < ignoreCellNum) {
                        continue;
                    }
                    row1.createCell(firstCellNum + 1 + j - ignoreCellNum).setCellValue(s);
                }
            }
        }
        jTextArea.append("共读取" + physicalNumberOfRows + "行" + "\n");
        System.out.println("共读取" + physicalNumberOfRows + "行" + "\n");
        workbook1.write(new FileOutputStream(f));
        firstCellNum = firstCellNum + totalCells;
    }

    /**
     * 判断是否需要循环继续
     *
     * @param name
     * @return
     */
    private boolean isContinue(String name) {
        if (!name.endsWith(".xlsx")) {
            return true;
        }
        // 过滤缓存文件
        if (name.startsWith("~$")) {
            return true;
        }
        return false;
    }

    /**
     * 判断文本类型
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                String s = String.valueOf(cell.getNumericCellValue() * 100);
                BigDecimal bigDecimal = new BigDecimal(s);
                //这里的 2 就是你要保留几位小数。
                double f1 = bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                String result = String.valueOf(f1) + "%";
                return result;
            case BLANK:
                return "";
            case ERROR:
                return "";
            default:
                return "";
        }
    }

    /**
     * 单例模式
     */
    private static ExcelBatchService instance = new ExcelBatchService();

    public static ExcelBatchService getInstance() {
        return instance;
    }
    private ExcelBatchService() {
    }

    public void setIgnoreCellNum(int ignoreCellNum) {
        this.ignoreCellNum = ignoreCellNum;
    }
}